﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class WarrantyDAL
    {

        #region[Warranty_SelectALL]
        public List<DTO.WarrantyDTO> Warranty_SelectALL()
        {
            List<DTO.WarrantyDTO> warall = new List<DTO.WarrantyDTO>();
            
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAllWarranty", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.WarrantyDTO war = new DTO.WarrantyDTO();
                war.WarrantyID = dr.GetInt32(0);
                war.SellMobileID = dr.GetInt32(1);
                war.Endday = dr.GetDateTime(2);
                warall.Add(war);
            }
            dr.Close();
            con.Close();

            return warall;
        }
        #endregion

        #region[Warranty_SelectByCustomerID]
        public DTO.WarrantyDTO Warranty_SelectByCustomerID(int id)
        {
            DTO.WarrantyDTO war = new DTO.WarrantyDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectWarrantyByCustomerID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@customerid", id);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                war.WarrantyID = dr.GetInt32(0);
                war.SellMobileID = dr.GetInt32(1);
                war.Endday = dr.GetDateTime(2);
            }
            dr.Close();
            con.Close();
            return war;
        }
        #endregion

        #region[Warranty_SelectByID]
        public DTO.WarrantyDTO Warranty_SelectByID(int id)
        {
            DTO.WarrantyDTO war = new DTO.WarrantyDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectWarrantyByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@warrantyid", id);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                war.WarrantyID = dr.GetInt32(0);
                war.SellMobileID = dr.GetInt32(1);
                war.Endday = dr.GetDateTime(2);
            }
            dr.Close();
            con.Close();
            return war;
        }
        #endregion

        #region[Warranty_SelectImageByValue]
        public DTO.WarrantyDTO Warranty_SelectImageByValue(byte[] image)
        {
            DTO.WarrantyDTO war = new DTO.WarrantyDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectImageByValue", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@image", image);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                war.WarrantyID = dr.GetInt32(0);
                war.SellMobileID = dr.GetInt32(1);
                war.Endday = dr.GetDateTime(2);
            }
            dr.Close();
            con.Close();
            return war;
        }
        #endregion

        #region[Warranty_SelectBySellMoblieID]
        public DTO.WarrantyDTO Warranty_SelectBySellMoblieID(int id)
        {
            DTO.WarrantyDTO war = new DTO.WarrantyDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectWarrantyBySellMobileID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@sellmobileid", id);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                war.WarrantyID = dr.GetInt32(0);
                war.SellMobileID = dr.GetInt32(1);
                war.Endday = dr.GetDateTime(2);
            }
            dr.Close();
            con.Close();
            return war;
        }
        #endregion

        #region[Warranty_SelectByCustomerName]
        public DataTable Warranty_SelectByCustomerName(string name)
        {
            DataTable dataTable = new DataTable();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectWarrantyByCustomerName", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new MySqlParameter("@name", name));

            con.Open();
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                da.Fill(dataTable);
            }
            con.Close();

            return dataTable;
        }
        #endregion

        #region [Warranty_Insert]
        public bool Warranty_Insert(DTO.WarrantyDTO war)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("AddWarranty", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@sellmobileid", war.SellMobileID));
            cmd.Parameters.Add(new MySqlParameter("@endday", war.Endday));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

    }
}
